alter table jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt  set tblproperties ('external.table.purge'='true');
drop table jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
CREATE EXTERNAL TABLE jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt(
  province_id string COMMENT '省份id', 
  province string COMMENT '省份', 
  network_code string COMMENT '网点编码', 
  network_name string COMMENT '网点名称', 
  abnormal_reason_code string COMMENT '筛单原因编码', 
  abnormal_reason string COMMENT '筛单原因', 
  effect_time_start date COMMENT '生效日期', 
  effect_time_end date COMMENT '失效日期', 
  is_incr tinyint COMMENT '是否新增', 
  is_loss tinyint COMMENT '是否减少', 
  cnt_date date COMMENT '数据日期', 
  virt_code string COMMENT '虚拟代理区code', 
  virt_name string COMMENT '虚拟代理区name', 
  belong_proxy string COMMENT '代理区名称', 
  belong_proxy_code string COMMENT '代理区编码', 
  date_time date COMMENT '统计时间')
COMMENT '京津网点异常分析-筛单明细'
PARTITIONED BY ( 
  dt string COMMENT '时间分区', 
  dy string COMMENT '数据日期')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_sqs_abnormal_network_interceptor_detail_dt'
TBLPROPERTIES ( 'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');


create external table jms_dm.dm_sqs_abnormal_network_cnt_dt
(
agent_code string comment '代理区或者虚拟大区编码'
,agent_name string comment '代理区或者虚拟大区名称'
,network_cnt int comment '网点数'
,before_networ_cnt int comment '上一天网点数'
,incr_networ_cnt int comment '新增网点数'
,network_cnt_rt decimal(16,2) comment '环比 *100后的'
,low_networ_cnt int comment'减少网点数'
,target_value int comment '目标值'
,over_value int comment '超出目标值'
,data_type string comment '统计类型 筛单 、爆仓'
,date_time date comment '统计时间'
,agent_type string comment '类型：虚拟区、代理区'
)comment '京津网点异常分析-筛单 爆仓'
PARTITIONED BY ( dt string COMMENT '时间分区',dy string comment 'shaidan/baocang')
stored as parquet
LOCATION '/dw/hive/bidefault.db/external/dm_sqs_abnormal_network_cnt_dt'
TBLPROPERTIES ( 'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');

alter table jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt add columns
(
     area_code  string comment'网管片区code'
     ,area_name string comment'网管片区名称'
)cascade

